package com.poi2html;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.usermodel.Picture;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;

/**
 * 这个测试类2003转换可以，07样式不对。请使用下面那个类POIReadExcelToHtml
 * @author Administrator
 *
 */
public class PoiExcelToHtml {

	/**
	 * 2007版本word转换成html
	 * 
	 * @throws IOException
	 */
	public static void Word2007ToHtml() throws IOException {
//		String htmlname = "exportExcel" + sourceid + ".html";
//		String path = request.getSession().getServletContext().getRealPath("/view/excel");
//		String filename = request.getSession().getServletContext().getRealPath("/vod/mp4");
		String filepath= "C:/Users/Administrator/Desktop/";
		String filename = "服务器资源分配.xlsx";
		String path ="C:/Users/Administrator/Desktop/";
		String htmlname = "服务器资源分配.html";
//		fileExists(path);
		Workbook workbook = null;
		InputStream is = new FileInputStream(filepath+filename);
		try {
			String html = "";
			workbook = new XSSFWorkbook(is);
			for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
				Sheet sheet = workbook.getSheetAt(numSheet);
				if (sheet == null) {
					continue;
				}
				html += "=======================" + sheet.getSheetName() + "=========================<br><br>";

				int firstRowIndex = sheet.getFirstRowNum();
				int lastRowIndex = sheet.getLastRowNum();
				html += "<table border='1' align='left'>";
				Row firstRow = sheet.getRow(firstRowIndex);
				for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
					Cell cell = firstRow.getCell(i);
					String cellValue = getCellValue(cell, true);
					html += "<th>" + cellValue + "</th>";
				}

				// 行
				for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
					Row currentRow = sheet.getRow(rowIndex);
					html += "<tr>";
					if (currentRow != null) {

						int firstColumnIndex = currentRow.getFirstCellNum();
						int lastColumnIndex = currentRow.getLastCellNum();
						// 列
						for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
							Cell currentCell = currentRow.getCell(columnIndex);
							String currentCellValue = getCellValue(currentCell, true);
							html += "<td>" + currentCellValue + "</td>";
						}
					} else {
						html += " ";
					}
					html += "</tr>";
				}
				html += "</table>";

				ByteArrayOutputStream outStream = new ByteArrayOutputStream();
				DOMSource domSource = new DOMSource();
				StreamResult streamResult = new StreamResult(outStream);

				TransformerFactory tf = TransformerFactory.newInstance();
				Transformer serializer = tf.newTransformer();
				serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
				serializer.setOutputProperty(OutputKeys.INDENT, "yes");
				serializer.setOutputProperty(OutputKeys.METHOD, "html");
				serializer.transform(domSource, streamResult);
				outStream.close();
				FileUtils.writeStringToFile(new File(path, htmlname), html, "gbk");
			}
		} catch (Exception e) {
			e.printStackTrace();

		}
	}

	/**
	 * 2003版本excel转换成html
	 * 
	 * @throws IOException
	 */
	public static void Word2003ToHtml() throws Exception {
		String substring = PoiExcelToHtml.class.getResource("/").toString().substring(6);
		String path = "C:\\Users\\Administrator\\Desktop\\";
		String file = "测试文档.xls";

		InputStream input = new FileInputStream(substring + file);
		HSSFWorkbook excelBook = new HSSFWorkbook(input);
		ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(
				DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
		excelToHtmlConverter.processWorkbook(excelBook);
		//去掉Excel头行
		//excelToHtmlConverter.OutputColumnHeaders = false;
		excelToHtmlConverter.setOutputColumnHeaders(false);
		//去掉Excel行号
		excelToHtmlConverter.setOutputRowNumbers(false);
		List pics = excelBook.getAllPictures();
		
		if (pics != null) {
			for (int i = 0; i < pics.size(); i++) {
				Picture pic = (Picture) pics.get(i);
				try {
					pic.writeImageContent(new FileOutputStream(path + pic.suggestFullFileName()));
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				}
			}
		}
		Document htmlDocument = excelToHtmlConverter.getDocument();
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		DOMSource domSource = new DOMSource(htmlDocument);
		StreamResult streamResult = new StreamResult(outStream);
		TransformerFactory tf = TransformerFactory.newInstance();
		Transformer serializer = tf.newTransformer();
		serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
		serializer.setOutputProperty(OutputKeys.INDENT, "yes");
		serializer.setOutputProperty(OutputKeys.METHOD, "html");
		serializer.transform(domSource, streamResult);
		outStream.close();

		String content = new String(outStream.toByteArray());

		FileUtils.writeStringToFile(new File(path, "exportExcel.html"), content, "utf-8");
	}

	public static void main(String args[]) throws Exception {
		Word2003ToHtml();
//		Word2007ToHtml();
	}

	/**
	 * 读取单元格
	 * 
	 */
	private static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {return "";}
		if (treatAsStr) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
}
